<?php

namespace App\Utils;


use App\Handler\CompanyHandler;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;
use p5\objects\PropertyUtils;

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2015/8/12
 * Time: 9:36
 */
class ExcelImportUtils
{
    private $companyId;
    private $titles = [];
    private $firstLine = [];
    private $tableName = "";
    private $columnIndex = 1;
    private $mappings = "";

    public function loadIntoTable($file, $tableName = "", $sheetIndex = 0)
    {
        if (!empty($tableName)) {
            $this->tableName = $tableName;
        }
        $this->dropTable();
        Excel::selectSheetsByIndex($sheetIndex)->load($file, function ($reader) use ($tableName) {
            $this->loadData($reader, $tableName);
            return;
        });
    }

    private function loadData($reader, $tableName)
    {
        $reader->noHeading();
        $data = $reader->all();
        if (empty($data)) {
            return;
        }
        $this->firstLine = ExcelReader::toSimpleValues($data[0]);
        $this->createTable($tableName);
        $this->saveData($data);
    }

    public function createTable($tableName)
    {
        $size = count($this->titles);
        $sql = "create table if not exists $tableName (";
        $sql .= "auto_id int not null auto_increment primary key ";

        for ($i = 0; $i < $size; $i++) {
            $title = $this->getTitle($i);
            $comment = $this->getComment($i);
            $type = $this->getType($i);
            $sql .= ", `$title` $type null comment '$comment'";
        }
        $sql .= ") engine=memory default charset=utf8";
        Log::debug($sql);
        DB::connection($this->companyDb())->statement($sql);
    }

    public function saveData($data)
    {
        $cols = count($this->firstLine);
        $size = count($data);
        for ($i = 1; $i < $size; $i++) {
            $line = $data[$i];
            $line = ExcelReader::toSimpleValues($line);
            $columns = [];
            $skip = false;
            for ($j = 0; $j < $cols; $j++) {
                $title = $this->getMappingName($j);
                if ($title == "ignore") {
                    continue;
                }
                $value = $this->getValue($line, $j);
                if (empty($value)) {
                    $nullable = $this->getNullable($j);
                    if ($nullable == false) {
                        $skip = true;
                        break;
                    }
                }
                $columns[$title] = $value;
            }
            if ($skip == true) {
                Log::debug("忽略导入第 $i 行");
                continue;
            }
            DB::connection($this->companyDb())->table($this->tableName)->insert($columns);
        }
    }

    public function getTitle($index)
    {
        $define = $this->getColumnDefine($index);
        if (empty($define)) {
            return "c_" . $this->columnIndex++;
        }
        return $define["name"];
    }

    public function getType($index)
    {
        $define = $this->getColumnDefine($index);
        $string = "varchar(64)";
        return PropertyUtils::getProperty($define, "type", $string);
    }

    public function getComment($index)
    {
        $define = $this->getColumnDefine($index);
        return PropertyUtils::getProperty($define, "label", '');
    }

    public function getNullable($index)
    {
        $define = $this->getColumnDefine($index);
        return PropertyUtils::getProperty($define, "nullable", true);
    }

    public function getMappingName($index)
    {
        $count = count($this->mappings);
        if ($index < $count) {
            return $this->mappings[$index];
        }
        return "ignore";
    }

    public function getColumnDefine($index)
    {
        if (count($this->titles) > $index) {
            return $this->titles[$index];
        }
        if (count($this->firstLine) > $index) {
            $name = $this->firstLine[$index];
            return ["name" => $name];
        }
        return '';
    }

    private function getValue($line, $j)
    {
        $value = "";
        if (count($line) > $j) {
            $value = $line[$j];
        }
        if (empty($value)) {
            if (count($this->titles) > $j) {
                $define = $this->titles[$j];
                // 看是否定义了缺省值
                $value = PropertyUtils::getProperty($define, "default_value", "");
            }
        }
        return $value;
    }

    /**
     * @return array
     */
    public function getTitles()
    {
        return $this->titles;
    }

    /**
     * @param array $titles
     */
    public function setTitles($titles)
    {
        $this->titles = $titles;
    }

    /**
     * @return string
     */
    public function getTableName()
    {
        return $this->tableName;
    }

    /**
     * @param string $tableName
     */
    public function setTableName($tableName)
    {
        $this->tableName = $tableName;
    }

    public function dropTable()
    {
        $sql = "drop table if exists " . $this->tableName;
        DB::connection($this->companyDb())->statement($sql);
    }

    /**
     * @return mixed
     */
    public function getCompanyId()
    {
        return $this->companyId;
    }

    /**
     * @param mixed $companyId
     */
    public function setCompanyId($companyId)
    {
        $this->companyId = $companyId;
    }

    /**
     * @return array
     */
    public function getMappings()
    {
        return $this->mappings;
    }

    /**
     * @param array $mappings
     */
    public function setMappings($mappings)
    {
        if (is_string($mappings)) {
            $mappings = explode(",", $mappings);
        }
        $this->mappings = $mappings;
    }

    public function companyDb()
    {
        return CompanyHandler::getDs($this->getCompanyId());
    }
}